package com.zhangyj.impl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.zhangyj.db.DBUtil;
import com.zhangyj.entitis.BProduct;
import com.zhangyj.util.Pagination;

public class DaoProduct {
	public void savePro(BProduct bPro) throws Exception {
		Connection	dbc = null;
		try {
			// int proId = DaoUtil.getMaxId("proid", "product");
			String sql = "insert into product (cpmc,cpgg, dj,dgj,yhj,sdj,jfj,beizhu,cplh,slug, pics,picb,cpbh,code,xh) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			dbc = DBUtil.getConnection();
			PreparedStatement ps = dbc.prepareStatement(sql);
			ps.setString(1, bPro.getCpmc());
			ps.setString(2, bPro.getCpgg());
			ps.setDouble(3, bPro.getDj());
			ps.setDouble(4, bPro.getDgj());
			ps.setDouble(5, bPro.getYhj());
			ps.setDouble(6, bPro.getSdj());
			ps.setDouble(7, bPro.getJfj());
			ps.setString(8, bPro.getBeizhu());
			ps.setString(9, bPro.getCplh());
			ps.setString(10, bPro.getSlug());
			ps.setString(11, bPro.getPics());
			ps.setString(12, bPro.getPicb());
			ps.setInt(13, DBUtil.getIntId("product"));
			ps.setString(14, bPro.getCode());
			ps.setString(15, bPro.getXh());
			ps.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (dbc != null)
				dbc.close();
		}
	}

	public void saveOrder(int cpbh, int sl, String hyxm, String shdh, String shdz, String shyb) throws Exception {
		Connection dbc = null;
		try {
			String sql = "insert into dingdan (cpbh,sl, hyxm,shdh,shdz,shyb) values(?,?,?,?,?,?)";
			dbc = DBUtil.getConnection();
			PreparedStatement ps = dbc.prepareStatement(sql);
			ps.setInt(1, cpbh);
			ps.setInt(2, sl);
			ps.setString(3, hyxm);
			ps.setString(4, shdh);
			ps.setString(5, shdz);
			ps.setString(6, shyb);
			ps.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (dbc != null)
				dbc.close();
		}
	}

	public List<BProduct> getList(Pagination p, String cplh) throws Exception {
		List<BProduct> list = new ArrayList<BProduct>();
		Connection dbc = null;
		ResultSet rs = null;
		try {
			String sql = "select a.cpbh,a.cpmc,a.dj,a.jfj,a.pics,a.slug from product a,producefl b where a.cplh= b.cplh and a.cplh = ?";
			p.setTotalCount(getCountFromFl(cplh));
			sql = p.builderSql(sql, "cpbh", "a", false);
			dbc = DBUtil.getConnection();
			PreparedStatement ps = dbc.prepareStatement(sql);

			ps.setString(1, cplh);
			rs = ps.executeQuery();
			while (rs.next()) {
				BProduct bPro = new BProduct();
				bPro.setCpbh(rs.getInt(1));
				bPro.setCpmc(rs.getString(2));
				bPro.setDj(rs.getDouble(3));
				bPro.setJfj(rs.getDouble(4));
				bPro.setPics(rs.getString(5));
				bPro.setSlug(rs.getString(6));
				list.add(bPro);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (dbc != null)
				dbc.close();
		}
		return list;
	}

	public List<BProduct> getProList(Pagination p,String con) throws Exception {
		List<BProduct> list = new ArrayList<BProduct>();
		Connection dbc = null;
		ResultSet rs = null;
		try {
			String sql = "select cpbh,cpmc,producefl.cplh,cplm,slug,xh,dj,code from product,producefl where product.cplh = producefl.cplh";
			boolean hasCon = con != null && con.length() > 0;
			if (hasCon) {
				sql +=" and cpmc like ?";
			}
			p.setTotalCount(getCount(con));
			sql = p.builderSql(sql, "cpbh", "product", true);
			dbc = DBUtil.getConnection();
			PreparedStatement ps = dbc.prepareStatement(sql);
			if (hasCon) {
				ps.setString(1, "%" + con + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				BProduct bPro = new BProduct();
				bPro.setCpbh(rs.getInt(1));
				bPro.setCpmc(rs.getString(2));
				bPro.setCplh(rs.getString(3));
				bPro.setCplm(rs.getString(4));
				bPro.setSlug(rs.getString(5));
				bPro.setXh(rs.getString(6));
				bPro.setDj(rs.getDouble(7));
				bPro.setCode(rs.getString(8));
				list.add(bPro);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (dbc != null)
				dbc.close();
		}
		return list;
	}

	public BProduct getContent(String slug) throws Exception {
		BProduct bPro = new BProduct();
		Connection dbc = null;
		ResultSet rs = null;
		try {
			String sql = "select cpbh,cpmc,cpgg, dj,dgj,yhj,sdj,jfj,beizhu,product.cplh, pics,picb,code,xh from product,producefl where slug = ? and producefl.cplh = product.cplh";
			dbc = DBUtil.getConnection();
			dbc.prepareStatement(sql);
			PreparedStatement ps = dbc.prepareStatement(sql);
			ps.setString(1, slug);
			rs = ps.executeQuery();
			if (rs.next()) {
				bPro.setCpbh(rs.getInt(1));
				bPro.setCpmc(rs.getString(2));
				bPro.setCpgg(rs.getString(3));
				bPro.setDj(rs.getDouble(4));
				bPro.setDgj(rs.getDouble(5));
				bPro.setYhj(rs.getDouble(6));
				bPro.setSdj(rs.getDouble(7));
				bPro.setJfj(rs.getDouble(8));
				bPro.setBeizhu(rs.getString(9));
				bPro.setSlug(slug);
				bPro.setCplh(rs.getString(10));
				bPro.setPics(rs.getString(11));
				bPro.setPicb(rs.getString(12));
				bPro.setCode(rs.getString(13));
				bPro.setXh(rs.getString(14));
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			throw e;
		} finally {
			if (dbc != null)
				dbc.close();
		}
		return bPro;
	}

	public void del(String id) throws Exception {
		Connection dbc = null;
		try {
			String sql = "delete  from product where cpbh =?";
			dbc = DBUtil.getConnection();
			dbc.prepareStatement(sql);
			PreparedStatement ps = dbc.prepareStatement(sql);
			ps.setInt(1, Integer.parseInt(id));
			ps.execute();
			ps.close();
		} catch (Exception e) {
			throw e;
		} finally {
			if (dbc != null)
				dbc.close();
		}
	}

	public BProduct getProCon(String cpbh) throws Exception {
		BProduct bPro = new BProduct();
		Connection dbc = null;
		ResultSet rs = null;
		try {
			String sql = "select cpbh,cpmc,cpgg, dj,dgj,yhj,sdj,jfj,beizhu,product.cplh, pics,picb,slug from product,producefl where cpbh = ? and producefl.cplh = product.cplh";
			dbc = DBUtil.getConnection();
			PreparedStatement ps = dbc.prepareStatement(sql);
			ps.setString(1, cpbh);
			rs = ps.executeQuery();
			if (rs.next()) {
				bPro.setCpbh(rs.getInt(1));
				bPro.setCpmc(rs.getString(2));
				bPro.setCpgg(rs.getString(3));
				bPro.setDj(rs.getDouble(4));
				bPro.setDgj(rs.getDouble(5));
				bPro.setYhj(rs.getDouble(6));
				bPro.setSdj(rs.getDouble(7));
				bPro.setJfj(rs.getDouble(8));
				bPro.setBeizhu(rs.getString(9));
				bPro.setCplh(rs.getString(10));
				bPro.setPics(rs.getString(11));
				bPro.setPicb(rs.getString(12));
				bPro.setSlug(rs.getString(13));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (dbc != null)
				dbc.close();
		}
		return bPro;
	}

	public void saveMod(BProduct bPro) throws Exception {
		Connection dbc = null;
		try {
			String sql = "update product set cpmc =?, cpgg=?, dj=?, dgj=?, yhj=?, sdj=?, jfj=?, beizhu=?,slug =?, cplh=?,xh=? where cpbh = ?";
			dbc = DBUtil.getConnection();
			PreparedStatement ps = dbc.prepareStatement(sql);
			ps.setString(1, bPro.getCpmc());
			ps.setString(2, bPro.getCpgg());
			ps.setDouble(3, bPro.getDj());
			ps.setDouble(4, bPro.getDgj());
			ps.setDouble(5, bPro.getYhj());
			ps.setDouble(6, bPro.getSdj());
			ps.setDouble(7, bPro.getJfj());
			ps.setString(8, bPro.getBeizhu());
			ps.setString(9, bPro.getSlug());
			ps.setString(10, bPro.getCplh());
			ps.setString(11, bPro.getXh());
			ps.setInt(12, bPro.getCpbh());
			ps.executeUpdate();
			ps.close();
		} catch (Exception e) {
			throw e;
		} finally {
			if (dbc != null)
				dbc.close();
		}
	}

	public BProduct getLastOne(String sortId) throws Exception {
		BProduct bPro = new BProduct();
		Connection dbc = null;
		ResultSet rs = null;
		try {
			String sql = "select top 1 cpbh,cpmc, dj,dgj,yhj,sdj,jfj, pics, slug from product,producefl where producefl.cplh = product.cplh and product.cplh = ? order by product.cpbh desc";
			dbc = DBUtil.getConnection();
			PreparedStatement ps = dbc.prepareStatement(sql);
			ps.setString(1, sortId);

			rs = ps.executeQuery();
			if (rs.next()) {
				bPro.setCpbh(rs.getInt(1));
				bPro.setCpmc(rs.getString(2));
				bPro.setDj(rs.getDouble(3));
				bPro.setDgj(rs.getDouble(4));
				bPro.setYhj(rs.getDouble(5));
				bPro.setSdj(rs.getDouble(6));
				bPro.setJfj(rs.getDouble(7));
				bPro.setPics(rs.getString(8));
				bPro.setSlug(rs.getString(9));

			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (dbc != null)
				dbc.close();
		}
		return bPro;
	}

	// for manage get list
	 int getCount(String con) throws Exception {
		Connection dbc = null;
		try {
			boolean hasCon = con != null && con.length() > 0;
			String sql = "select count(*) from product";
			if (hasCon) {
				sql += " where cpmc like ?";
			}
			dbc = DBUtil.getConnection();
			PreparedStatement ps = dbc.prepareStatement(sql);
			if (hasCon) {
				ps.setString(1, "%" + con + "%");
			}
			ResultSet rs = ps.executeQuery();
			int count = 0;
			if (rs.next()) {
				count = rs.getInt(1);
			}
			rs.close();
			ps.close();
			return count;
		} catch (Exception e) {
			throw e;
		} finally {
			if (dbc != null)
				dbc.close();
		}
	}

	// For get the count by sort
	 int getCountFromFl(String cplh) throws Exception {
		Connection dbc = null;
		try {
			String sql = "select count(*) from product where cplh = ?";
			dbc = DBUtil.getConnection();
			PreparedStatement ps = dbc.prepareStatement(sql);
			ps.setString(1, cplh);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			if (rs.next()) {
				count = rs.getInt(1);
			}
			rs.close();
			ps.close();
			return count;
		} catch (Exception e) {
			throw e;
		} finally {
			if (dbc != null)
				dbc.close();
		}
	}

}